/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.Applicant;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import utility.DatabaseConnection;

/**
 *
 * @author SonLT
 */
public class ApplicantModel {

    private String sql;
    private Connection con = null;
    private CallableStatement cs = null;
    private ResultSet rs = null;
    private PreparedStatement ps = null;

    //Get all Applicant.
    public ArrayList<Applicant> getallApplicant() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getAllApplicant}";
            ArrayList<Applicant> listA = new ArrayList<Applicant>();
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            while (rs.next()) {
                Applicant vs = new Applicant();
                vs.setApplicantID(rs.getString(1));
                vs.setUsername(rs.getString(2));
                vs.setPassword(rs.getString(3));
                vs.setApplicantName(rs.getString(4));
                vs.setApplicantPhone(rs.getString(5));
                vs.setApplicantEmail(rs.getString(6));
                vs.setApplicantAbout(rs.getString(7));
                vs.setDateCreate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                listA.add(vs);
            }
            rs.close();
            return listA;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //Insert a new Applicant.
    public boolean addApplicant(Applicant a) {
        try {
            sql = "{call sp_addNewApplicant(?,?,?,?,?,?,?,?,?)}";
            con = DatabaseConnection.getConnection();
            cs = con.prepareCall(sql);
            cs.setString(1, a.getApplicantID());
            cs.setString(2, a.getUsername());
            cs.setString(3, a.getPassword());
            cs.setString(4, a.getApplicantName());
            cs.setString(5, a.getApplicantPhone());
            cs.setString(6, a.getApplicantEmail());
            cs.setString(7, a.getApplicantAbout());
            cs.setString(8, a.getDateCreate());
            cs.setString(9, a.getStatus());
            int result = cs.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Get one applicant to update.
    public Applicant getOneApplicant(String id) {
        Applicant a = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call getOneApplicant(?)}";
            cs = con.prepareCall(sql);
            cs.setString(1, id);
            rs = cs.executeQuery();

            while (rs.next()) {
                a = new Applicant();
                a.setApplicantID(rs.getString(1));
                a.setUsername(rs.getString(2));
                a.setPassword(rs.getString(3));
                a.setApplicantName(rs.getString(4));
                a.setApplicantPhone(rs.getString(5));
                a.setApplicantEmail(rs.getString(6));
                a.setApplicantAbout(rs.getString(7));
                a.setDateCreate(rs.getString(8));
                a.setStatus(rs.getString(9));
            }

            return a;

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //Update a applicant.
    public boolean updateApplicant(Applicant a) {
        try {
            sql = "UPDATE Applicant SET Username = ?, Password = ?, ApplicantName = ?, AppPhone = ?, AppEmail = ?, AppAbout = ?, DateCreate = ?, Status = ? WHERE ApplicantID = ?";
            con = DatabaseConnection.getConnection();
            cs = con.prepareCall(sql);
            cs.setString(1, a.getUsername());
            cs.setString(2, a.getPassword());
            cs.setString(3, a.getApplicantName());
            cs.setString(4, a.getApplicantPhone());
            cs.setString(5, a.getApplicantEmail());
            cs.setString(6, a.getApplicantAbout());
            cs.setString(7, a.getDateCreate());
            cs.setString(8, a.getStatus());
            cs.setString(9, a.getApplicantID());
            int result = cs.executeUpdate();
            cs.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean updateStatusApplicant(String status, String aid){
        try {
            sql = "UPDATE Applicant SET Status = ? WHERE ApplicantID = ?";
            con = DatabaseConnection.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, status);
            ps.setString(2, aid);

            int result = ps.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Delete a Applicant
    public boolean deleteApplicant(String applicantID) {
        try {
            sql = "{call sp_deleteApplicant(?)}";
            con = DatabaseConnection.getConnection();
            cs = con.prepareCall(sql);
            cs.setString(1, applicantID);
            int result = cs.executeUpdate();
            cs.close();
            con.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Fill by Username
    public ArrayList<Applicant> getApplicantByUser(String usernames) {
        ArrayList<Applicant> listApp = new ArrayList<Applicant>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getApplicantByUsername(?)}";
            
            cs = con.prepareCall(sql);
            cs.setString(1, usernames);
            rs = cs.executeQuery();
            while (rs.next()) {
                Applicant a = new Applicant();
                a.setApplicantID(rs.getString(1));
                a.setUsername(rs.getString(2));
                a.setPassword(rs.getString(3));
                a.setApplicantName(rs.getString(4));
                a.setApplicantPhone(rs.getString(5));
                a.setApplicantEmail(rs.getString(6));
                a.setApplicantAbout(rs.getString(7));
                a.setDateCreate(rs.getString(8));
                a.setStatus(rs.getString(9));
                listApp.add(a);
            }
            rs.close();
            return listApp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //Fill by Email
    public ArrayList<Applicant> getApplicantByEmail(String email) {
        ArrayList<Applicant> listApp = new ArrayList<Applicant>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getApplicantByEmail(?)}";
            
            cs = con.prepareCall(sql);
            cs.setString(1, email);
            rs = cs.executeQuery();
            while (rs.next()) {
                Applicant a = new Applicant();
                a.setApplicantID(rs.getString(1));
                a.setUsername(rs.getString(2));
                a.setPassword(rs.getString(3));
                a.setApplicantName(rs.getString(4));
                a.setApplicantPhone(rs.getString(5));
                a.setApplicantEmail(rs.getString(6));
                a.setApplicantAbout(rs.getString(7));
                a.setDateCreate(rs.getString(8));
                a.setStatus(rs.getString(9));
                listApp.add(a);
            }
            rs.close();
            return listApp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //List applicant must be interviewed.
    public ArrayList<Applicant> getCandidate() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_notInProcess}";
            ArrayList<Applicant> listVan = new ArrayList<Applicant>();
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            while (rs.next()) {
                Applicant vs = new Applicant();
                vs.setApplicantID(rs.getString(1));
                vs.setUsername(rs.getString(2));
                vs.setPassword(rs.getString(3));
                vs.setApplicantName(rs.getString(4));
                vs.setApplicantPhone(rs.getString(5));
                vs.setApplicantEmail(rs.getString(6));
                vs.setApplicantAbout(rs.getString(7));
                vs.setDateCreate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //List applicant must be interviewed.
    public String getcountApp() {
        try {
            String s = null;
            con = DatabaseConnection.getConnection();
            sql = "{call sp_countProcess}";
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            if(rs.next())
            {
                s = rs.getString(1);
            }
            rs.close();            
            return s;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //Fill Status
    public ArrayList<Applicant> getApplicantByStatus(String status) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_viewApp(?)}";
            ArrayList<Applicant> listApp = new ArrayList<Applicant>();
            cs = con.prepareCall(sql);
            cs.setString(1, status);
            rs = cs.executeQuery();
            while (rs.next()) {
                Applicant a = new Applicant();
                a.setApplicantID(rs.getString(1));
                a.setUsername(rs.getString(2));
                a.setPassword(rs.getString(3));
                a.setApplicantName(rs.getString(4));
                a.setApplicantPhone(rs.getString(5));
                a.setApplicantEmail(rs.getString(6));
                a.setApplicantAbout(rs.getString(7));
                a.setDateCreate(rs.getString(8));
                a.setStatus(rs.getString(9));
                listApp.add(a);
            }
            rs.close();
            return listApp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean checkLoginUser(String username, String password) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Applicant where Username=? and Password=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public Applicant getApplicantByUsername(String username) {
        Applicant a = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Applicant where Username =?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();

            while (rs.next()) {
                a = new Applicant();
                a.setApplicantID(rs.getString(1));
                a.setUsername(rs.getString(2));
                a.setPassword(rs.getString(3));
                a.setApplicantName(rs.getString(4));
                a.setApplicantPhone(rs.getString(5));
                a.setApplicantEmail(rs.getString(6));
                a.setApplicantAbout(rs.getString(7));
                a.setDateCreate(rs.getString(8));
                a.setStatus(rs.getString(9));
            }

            return a;

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
}
